package com.hy.dao.impl;

import com.hy.bean.RespBean;
import com.hy.dao.PrintManagementDao;
import com.hy.exception.BizException;
import com.hy.utils.DBManager;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class PrintManagementDaoImpl implements PrintManagementDao {
    /**
     * 打印管理 dao层 实现类
     */
    private static QueryRunner qr = new QueryRunner();
    private static BasicRowProcessor basicRowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());

    @Override
    public List<Map<String, Object>> selectSalesMessage(Integer currentPage,Integer pageSize) {

        List<Map<String, Object>> mapList = null;
        try {
            Connection conn = DBManager.getConnection();

            String sql = "select t1.*,t2.*,t3.*,t4.*," +
                    "t1.id ordersPartstableId," +
                    "t2.id orderstableId," +
                    "t3.id carpartstableId," +
                    "t4.id clienttableId," +
                    "t2.remark orderstableRemark," +
                    "t4.remark clienttableRemark " +
                    "from orders_partstable t1, orderstable t2, carpartstable t3, clienttable t4 " +
                    "where t1.ordersTable_id = t2.id and t1.partsTable_id = t3.id and t2.client_id = t4.id limit ?,?";

            mapList = qr.query(conn, sql, new MapListHandler(basicRowProcessor),(currentPage-1)*pageSize,pageSize);

            DbUtils.close(conn);
        } catch (Exception e) {
            throw new BizException("数据库连接异常", e);
        }

        return mapList;
    }

    @Override
    public Long countSalesMessage() {
        long count = 0;
        try {
            Connection conn = DBManager.getConnection();

            String sql = "select count(*) " +
                    "from orders_partstable t1, orderstable t2, carpartstable t3, clienttable t4 " +
                    "where t1.ordersTable_id = t2.id and t1.partsTable_id = t3.id and t2.client_id = t4.id";

            count = qr.query(conn, sql, new ScalarHandler<>());

            DbUtils.close(conn);
        } catch (Exception e) {
            throw new BizException("数据库连接异常", e);
        }

        return count;
    }

    @Override
    public List<Map<String, Object>> selectInventory(Integer currentPage, Integer pageSize) {
        List<Map<String, Object>> maps = null;

        try {
            Connection conn = DBManager.getConnection();

            String sql = "select * from carpartstable t1  " +
                        "left join (select " +
                        " i1.partTable_id, " +
                        " sum(i1.inStoreCount) sumInStoreCount " +
                        " from instore_partstable i1 " +
                        " join instoretable i2 on i1.inStoreTable_id = i2.id " +
                        " group by i1.partTable_id " +
                        " ) t2 on t1.id = t2.partTable_id " +
                        "left join " +
                        " (select " +
                        " o1.partTable_id, " +
                        " sum(o1.outStoreNumber) outStoreCount  " +
                        " from outsotre_ordertable o1 " +
                        " join outstoretable o2 on o1.outStoreTable_id = o2.id " +
                        " group by o1.partTable_id " +
                        ") t3 on t1.id = t3.partTable_id limit ?,?";

            maps = qr.query(conn,sql,new MapListHandler(basicRowProcessor),(currentPage-1)*pageSize,pageSize);

            DbUtils.close(conn);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        return maps;
    }

    @Override
    public Long countInventory() {
        long count = 0;
        try {
            Connection conn = DBManager.getConnection();

            String sql = "select count(*) from carpartstable t1  " +
                    "left join (select " +
                    " i1.partTable_id, " +
                    " sum(i1.inStoreCount) sumInStoreCount " +
                    " from instore_partstable i1 " +
                    " join instoretable i2 on i1.inStoreTable_id = i2.id " +
                    " group by i1.partTable_id " +
                    " ) t2 on t1.id = t2.partTable_id " +
                    "left join " +
                    " (select " +
                    " o1.partTable_id, " +
                    " sum(o1.outStoreNumber) outStoreCount  " +
                    " from outsotre_ordertable o1 " +
                    " join outstoretable o2 on o1.outStoreTable_id = o2.id " +
                    " group by o1.partTable_id " +
                    ") t3 on t1.id = t3.partTable_id";

            count = qr.query(conn, sql, new ScalarHandler<>());

            DbUtils.close(conn);
        } catch (Exception e) {
            throw new BizException("数据库连接异常", e);
        }

        return count;
    }

    @Override
    public List<Map<String, Object>> selectPurchase(Integer currentPage, Integer pageSize) {
        List<Map<String, Object>> maps = null;

        try {
            Connection conn = DBManager.getConnection();

            String sql = " select " +
                    "buy.buyNumber, " +
                    "car.partName, " +
                    "part.buyCount, " +
                    "buy.buyTime, " +
                    "buy.reachTime, " +
                    "buy.requestBuyer, " +
                    "buy.requestTime, " +
                    "part.reachCount, " +
                    "buy.totalPrice " +
                    "from buytable buy " +
                    "join buy_parttable part on buy.id = part.buyTable_id " +
                    "join carpartstable car on part.partTable_id = car.id " +
                    "limit ?,?";


            maps = qr.query(conn,sql,new MapListHandler(basicRowProcessor),(currentPage-1)*pageSize,pageSize);

            DbUtils.close(conn);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        return maps;
    }

    @Override
    public Long countPurchase() {
        long count = 0;
        try {
            Connection conn = DBManager.getConnection();

            String sql = " select count(*) " +
                    "from buytable buy join buy_parttable part on buy.id = part.buyTable_id join carpartstable car on part.partTable_id = car.id";

            count = qr.query(conn, sql, new ScalarHandler<>());

            DbUtils.close(conn);
        } catch (Exception e) {
            throw new BizException("数据库连接异常", e);
        }

        return count;
    }

}
